DROP SEQUENCE USER_SEQ;
DROP SEQUENCE QUES_SEQ;
CREATE SEQUENCE USER_SEQ START WITH 1000000 INCREMENT BY 1;
CREATE SEQUENCE QUES_SEQ START WITH 10000000 INCREMENT BY 1;
DROP TABLE ANSWER;
DROP TABLE QUESTION;
DROP TABLE STUDENT;
DROP TABLE FACULTY;
DROP TABLE LOGIN_DETAILS;
DROP TABLE STREAM_YEAR_SUBJECT;
DROP TABLE SUBJECTS;
DROP TABLE YEAR;
DROP TABLE STREAM;
CREATE TABLE STREAM(STREAM_NAME VARCHAR2(10) PRIMARY KEY);
INSERT INTO STREAM VALUES('BBA');
INSERT INTO STREAM VALUES('BCA');
CREATE TABLE YEAR(YEAR_ID VARCHAR2(6) UNIQUE);
INSERT INTO YEAR VALUES('First');
INSERT INTO YEAR VALUES('Second');
INSERT INTO YEAR VALUES('Third');
CREATE TABLE SUBJECTS (SUBJECT_NAME VARCHAR2(50) PRIMARY KEY);

CREATE TABLE STREAM_YEAR_SUBJECT (STREAM_FK VARCHAR2(10) REFERENCES STREAM (STREAM_NAME),
 YEAR_FK VARCHAR2(6) REFERENCES YEAR (YEAR_ID), SUBJECT_FK VARCHAR2(50) REFERENCES 
SUBJECTS (SUBJECT_NAME) ON DELETE CASCADE, PRIMARY KEY (STREAM_FK, YEAR_FK, SUBJECT_FK));

INSERT INTO SUBJECTS VALUES ('Digital Electronics');
INSERT INTO SUBJECTS VALUES ('Business Systems and Applications');
INSERT INTO SUBJECTS VALUES ('Introduction to Programming, Mathematics');
INSERT INTO SUBJECTS VALUES ('PC Software');
INSERT INTO SUBJECTS VALUES ('Computer Architecture and Systems');
INSERT INTO SUBJECTS VALUES ('Software Information Systems Analysis');
INSERT INTO SUBJECTS VALUES ('Design Computer Programming Mathematics');
INSERT INTO SUBJECTS VALUES ('English Language and Communication');
INSERT INTO SUBJECTS VALUES ('Operating Systems Data Structures with C');
INSERT INTO SUBJECTS VALUES ('Graphics '||'&'||' Internet');
INSERT INTO SUBJECTS VALUES ('Mathematics for Computing');
INSERT INTO SUBJECTS VALUES ('Management '||'&'||' Accounting');
INSERT INTO SUBJECTS VALUES ('Data Base Management System');
INSERT INTO SUBJECTS VALUES ('Object-Oriented Programming with C++');
INSERT INTO SUBJECTS VALUES ('Software Project Management and Quality Assurance');
INSERT INTO SUBJECTS VALUES ('Statistics, Numerical Methods '||'&'||' Algorithms');
INSERT INTO SUBJECTS VALUES ('Environment and Ecology');
INSERT INTO SUBJECTS VALUES ('Data Communication '||'&'||' Computer Networks');
INSERT INTO SUBJECTS VALUES ('Unix and Shell Programming');
INSERT INTO SUBJECTS VALUES ('Values and Ethics of Profession');
INSERT INTO SUBJECTS VALUES ('Windows Programming');
INSERT INTO SUBJECTS VALUES ('JAVA');
INSERT INTO SUBJECTS VALUES ('Advanced Network');
INSERT INTO SUBJECTS VALUES ('English');
INSERT INTO SUBJECTS VALUES ('Mathematics');
INSERT INTO SUBJECTS VALUES ('Statistics');
INSERT INTO SUBJECTS VALUES ('Economics');
INSERT INTO SUBJECTS VALUES ('Indian Society '||'&'||' Culture');
INSERT INTO SUBJECTS VALUES ('Computer Applications');
INSERT INTO SUBJECTS VALUES ('Psychology');
INSERT INTO SUBJECTS VALUES ('Principles of Management');
INSERT INTO SUBJECTS VALUES ('Business Environment');
INSERT INTO SUBJECTS VALUES ('Business Regulatory Framework');
INSERT INTO SUBJECTS VALUES ('Business Communication');
INSERT INTO SUBJECTS VALUES ('Financial Accounting');
INSERT INTO SUBJECTS VALUES ('Production Management');
INSERT INTO SUBJECTS VALUES ('Financial Management');
INSERT INTO SUBJECTS VALUES ('Marketing Management');
INSERT INTO SUBJECTS VALUES ('Human Resource Management');
INSERT INTO SUBJECTS VALUES ('Management Information Systems');
INSERT INTO SUBJECTS VALUES ('Environment Management');
INSERT INTO SUBJECTS VALUES ('Transport Management');
INSERT INTO SUBJECTS VALUES ('Health Care Management');
INSERT INTO SUBJECTS VALUES ('Social Research Methods');

INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'First', 'Digital Electronics');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'First', 'Business Systems and Applications');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'First', 'Introduction to Programming, Mathematics');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'First', 'PC Software');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'First', 'Computer Architecture and Systems');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'First', 'Software Information Systems Analysis');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'First', 'Design Computer Programming Mathematics');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'First', 'English Language and Communication');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'Second', 'Operating Systems Data Structures with C');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'Second', 'Graphics '||'&'||' Internet');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'Second', 'Mathematics for Computing');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'Second', 'Management '||'&'||' Accounting');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'Second', 'Data Base Management System');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'Second', 'Object-Oriented Programming with C++');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'Second', 'Software Project Management and Quality Assurance');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'Second', 'Statistics, Numerical Methods '||'&'||' Algorithms');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'Second', 'Environment and Ecology');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'Third', 'Data Communication '||'&'||' Computer Networks');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'Third', 'Unix and Shell Programming');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'Third', 'Values and Ethics of Profession');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'Third', 'Windows Programming');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'Third', 'JAVA');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BCA', 'Third', 'Advanced Network');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'First', 'English');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'First', 'Mathematics');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'First', 'Statistics');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'First', 'Economics');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'First', 'Indian Society '||'&'||' Culture');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'First', 'Computer Applications');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'First', 'Psychology');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'Second', 'Principles of Management');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'Second', 'Business Environment');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'Second', 'Business Regulatory Framework');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'Second', 'Business Communication');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'Second', 'Financial Accounting');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'Second', 'Production Management');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'Second', 'Management Information Systems');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'Third', 'Environment Management');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'Third', 'Transport Management');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'Third', 'Health Care Management');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'Third', 'Social Research Methods');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'Third', 'Financial Management');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'Third', 'Marketing Management');
INSERT INTO STREAM_YEAR_SUBJECT VALUES ('BBA', 'Third', 'Human Resource Management');

CREATE TABLE LOGIN_DETAILS (USER_ID INTEGER PRIMARY KEY, USER_NAME VARCHAR2(20) UNIQUE NOT NULL, PASSWORD
VARCHAR2(20) NOT NULL, TYPE VARCHAR2 (7) DEFAULT 'Admin' CHECK (TYPE IN('Admin', 'Student', 'Faculty')), FULL_NAME VARCHAR2(30) NOT NULL);
INSERT INTO LOGIN_DETAILS(USER_ID, USER_NAME, PASSWORD, FULL_NAME) VALUES (USER_SEQ.NEXTVAL,'admin','admin','ADMIN');
CREATE TABLE STUDENT(STUD_ID INTEGER PRIMARY KEY REFERENCES LOGIN_DETAILS(USER_ID) ON DELETE CASCADE, STREAM_FK VARCHAR2(10) REFERENCES STREAM(STREAM_NAME), YEAR_FK VARCHAR2(6) REFERENCES YEAR (YEAR_ID),
STUD_ROLL INTEGER NOT NULL);
CREATE TABLE FACULTY(FCLT_ID INTEGER PRIMARY KEY REFERENCES LOGIN_DETAILS(USER_ID) ON DELETE CASCADE, SUBJECT_FK VARCHAR2(50) REFERENCES SUBJECTS (SUBJECT_NAME));
CREATE TABLE QUESTION(QUES_ID INTEGER PRIMARY KEY, QUES_TEXT VARCHAR2(250), SUBJECT_FK VARCHAR2(50) REFERENCES SUBJECTS (SUBJECT_NAME) ON DELETE CASCADE,
ASKED_BY INTEGER NOT NULL REFERENCES LOGIN_DETAILS(USER_ID), QUES_TIME VARCHAR2(25), STATUS VARCHAR2(10) CHECK (
STATUS  IN('Open', 'Approved', 'Rejected')), REVIEWED_BY INTEGER);
CREATE TABLE ANSWER(QUES_FK INTEGER REFERENCES QUESTION(QUES_ID) ON DELETE CASCADE, ANS_TEXT VARCHAR2(250), ANS_BY INTEGER REFERENCES LOGIN_DETAILS(USER_ID), ANS_TIME VARCHAR2(25));
COMMIT;